<?php

namespace Admin\Controller;

use Think\Controller;

/**
 * Description of DBaseController
 * 基础数据导入导出
 * @author motsqueen
 */
class DBaseController extends Controller {

    public function _initialize() {
        header("Content-Type: text/html; charset=UTF-8");
        import("Org.Util.PHPExcel");
    }
    
    /**
     * 部门信息管理
     */
    public function dep_index(){
        // list news 
        $this->display();
        
    }
    
    public function deplist(){
        $page = I('get.page',1,'int');
        $rows = I('get.rows',10,'int');
        $db = M('depinfo');
        $ret['total'] = $db->count();
        //$firstRow = ($page-1)*$rows;
        $items = $db->order('did asc')->page($page,$rows)->select();        
        $ret['rows'] = $items;
        $this->ajaxReturn($ret);
    }
    
    public function depinfo() {
// 导入部门信息表格到表中 depinfo
// 表格  部门代号（4位）	系部名称
// depinfo：did              name  

        $info = $this->_upload();       
        $file = $info['xls'];
        $path = $file['savepath'] . $file['savename'];
        
        $cnt = 0;
        $tmpArray = array();
        $assc = array(
            'did' => 'A',
            'name' => 'B',
        );
        // "1dbase_depinfo.xlsx"
        $tmpArray = $this->_importData($path);
        $cnt = $this->_saveData('depinfo', $assc, 3, $tmpArray, 'did');

        echo "有" . $cnt . "条部门信息数据，导入成功！";
        echo "<a href='__APP__/Admin/DBase/dep_index'>点击返回</a>";
    }
    
    public function dep_newItem(){
        $did = I('post.did',0,'int'); 
        $name = I('post.name');

        $ret = array();

        $db = M('depinfo');
        $condition['did'] = $did;
        $flag = $db->where($condition)->find();
        if(!$flag){
            $data = array(
                'did' => $did,
                'name' => $name
            );
            $data = $db->create($data);
            if($data){
                $db->add($data);
                $ret['success'] = true;
            }
        } else {
            $ret['errorMsg'] = "该记录已经存在，不用添加！";            
        }
        $this->ajaxReturn($ret);
        
    }

    public function dep_editItem(){
        $id = I('get.id',0,'int');   
        $did = I('post.did');    
        $name = I('post.name');

        $ret = array(); 
        $db = M('depinfo');
        $condition['id'] = $id;
        $data['id'] = $id;
        $data['did'] = $did;
        $data['name'] = $name;
        $flag = $db->data($data)->save();
        if($flag){        
            $ret['success'] = true;          
        } else {
            $ret['errorMsg'] = $id; "修改出错，只能修改部门名称！";     
                 
        }
        $this->ajaxReturn($ret);

    }
    
    public function dep_destroy(){
        $did = I('post.did',0,'int');

        $ret = array();
        $db = M('depinfo');

        $condition['did'] = $did;
        $ids = $db->where($condition)->delete();
        if($ids>0){
            $ret['success'] = true;
        } elseif ($flag==0) {
            $ret['errorMsg'] = "没有删除数据";
        } else {
            $ret['errorMsg'] = "删除操作出错";
        }
        $this->ajaxReturn($ret);

    }
    
   // ################################################## 
   
    
   /**
    * 教职员工信息管理
    */
    public function staff_index(){
        // list news 
        $this->display();
        
    }
    

    public function stafflist(){
        $page = I('get.page',1,'int');
        $rows = I('get.rows',10,'int');
        //$db = M('Staffinfo');
        $db = D('StaffinfoView');
        $ret['total'] = $db->count();
        //$firstRow = ($page-1)*$rows;
        $items = $db->field('id,sid,name,sex,did,dep_name,role,status')->order('sid asc')->page($page,$rows)->select();        
        $ret['rows'] = $items;
        $this->ajaxReturn($ret);
    }
    
    public function getDepByDID(){
        $db = M('depinfo');
        $ret = $db->field('did','name')->order('did asc')->select();
        $this->ajaxReturn($ret);
    }

    public function staff_newItem(){
        $sid = I('post.sid');
        $name = I('post.name');
        $sex = I('post.sex','','int');
        $did  = I('post.did','','int');
        $role = I('post.role','1','int');
        $status = I('post.status','0','int');

        $data = array(
            'sid'   => $sid,
            'name'  => $name,
            'sex'   => $sex,
            'did'   => $did,
            'role'  => $role,
            'status'=> $status
        );

        $ret = array();        
        $db = M('staffinfo'); 

        
        $condition['sid'] = $sid;
        $ids = $db->where($condition)->find();
        if(!$ids){
            $data = $db->create($data);
            $db->data($data)->add();
            $ret['success'] = true;
        } else {
            $ret['errorMsg'] = "该记录已经存在，不用添加！"; 
        }

        $this->ajaxReturn($ret);


    }

    public function staff_editItem(){
        $id = I('post.id','','int');
        $sid = I('post.sid');
        $name = I('post.name');
        $sex = I('post.sex','','int');
        $did  = I('post.did','','int');
        $role = I('post.role','1','int');
        $status = I('post.status','0','int');

        $data = array(
            'id'    => $id,
            'sid'   => $sid,
            'name'  => $name,
            'sex'   => $sex,
            'did'   => $did,
            'role'  => $role,
            'status'=> $status
        );


        $ret = array(); 
        $db = M('staffinfo');
        $condition['id'] = $id;
        
        $ids = $db->data($data)->save();
        if($ids){        
            $ret['success'] = true;          
        } else {
            $ret['errorMsg'] = $id . "修改出错，只能修改部门名称！";     
                 
        }
        $this->ajaxReturn($ret);

    }

    public function staff_destroy(){
        $sid = I('post.sid',0,'int');
        $ret = array();

        $db = M('staffinfo');
        $condition['sid'] = $sid;
        $ids = $db->where($condition)->delete();
        if($ids>0){
            $ret['success'] = true;
        } elseif ($flag==0) {
            $ret['errorMsg'] = "没有删除数据";
        } else {
            $ret['errorMsg'] = "删除操作出错";
        }
        $this->ajaxReturn($ret);


    }

   

    public function staffinfo() {
    // 导入部门信息表格到表中 staffinfo
    // 表格:     教职工号(A) 姓名(B)    所属部门代码(C)   所属部门(D) 性别(E)    
    // staffinfo：  sid        name       did                           sex
        $info = $this->_upload();       
        $file = $info['xls'];
        $path = $file['savepath'] . $file['savename'];

        $cnt = 0;
        $tmpArray = array();
        $tmpArray = $this->_importData($path);
        // dump($tmpArray);
        $assc = array(
            'sid' => 'A',
            'name' => 'B',
            'did' => 'C',
            'sex' => 'E'
        );

        $cnt = $this->_saveData('staffinfo', $assc, 3, $tmpArray, 'sid');
        echo "有" . $cnt . "条教工信息数据，导入成功！";
        echo "<a href='/index.php'>点击返回首页</a>";
    }

    // ########################################### 专业管理

  /**
    * 教职员工信息管理
    */
    public function major_index(){
        // list news 
        $this->display();        
    }

    public function majorlist(){
        $page = I('get.page',1,'int');
        $rows = I('get.rows',10,'int');
        //$db = M('Staffinfo');
        $db = D('MajorView');
        $ret['total'] = $db->count();
        //$firstRow = ($page-1)*$rows;
        $items = $db->field('id,mid,name,did,depname')->order('mid asc')->page($page,$rows)->select();        
        $ret['rows'] = $items;
        $this->ajaxReturn($ret);
    }

    public function major_new(){        
        $ret = array();
        $data = array(
            'id'     => I('post.id','','int'),
            'mid'    => I('post.mid'),
            'name'   => I('post.name'),
            'did'    => I('post.did'),
        );
        $condition['id'] = $id;
        $db = M('Majinfo');
        $ids = $db->where($condition)->find();
        if(!$ids){
            $data = $db->create($data);
            $db->data($data)->add();
            $ret['success'] = true;
        } else {
            $ret['errorMsg'] = "该记录已经存在，不用添加！"; 
        }        
        $this->ajaxReturn($ret);
    }

    public function major_edit(){

        $data = array(
            'id'     => I('post.id','','int'),
            'mid'    => I('post.mid'),
            'name'   => I('post.name'),
            'did'    => I('post.did'),
        );

        $ret = array(); 
        $db = M('majinfo');
        $condition['id'] = $id;
        
        $ids = $db->data($data)->save();
        if($ids){        
            $ret['success'] = true;          
        } else {
            $ret['errorMsg'] = $id . "修改出错，只能修改部门名称！";     
                 
        }
        $this->ajaxReturn($ret);
    }

    public function major_delete(){
        $id = I('post.id',0,'int');
        $ret = array();

        $db = M('majinfo');
        $condition['id'] = $id;
        $ids = $db->where($condition)->delete();
        if($ids>0){
            $ret['success'] = true;
        } elseif ($flag==0) {
            $ret['errorMsg'] = "没有删除数据";
        } else {
            $ret['errorMsg'] = "删除操作出错";
        }
        $this->ajaxReturn($ret);

    }




    public function majorinfo() {
    // 导入专业信息表 majinfo
    // 专业代码A    专业名称B   所属系部代码C 所属系部D
    // mid           name            did      
        $assc = array(
            'mid' => 'A',
            'name' => 'B',
            'did' => 'C'
        );

        $info = $this->_upload();       
        $file = $info['xls'];
        $path = $file['savepath'] . $file['savename'];

        $cnt = 0;
        $tmpArray = array();
        $tmpArray = $this->_importData($path);
        $cnt = $this->_saveData('majinfo', $assc, 3, $tmpArray, 'mid');
        echo "有" . $cnt . "条专业信息数据，导入成功！";
        echo "<a href='__APP__/Admin/DBase/major_index'>点击返回首页</a>";
    }

    // ################################################ clsinfo

    public function cls_index(){
        $this->display();
    }

    public function clslist(){
        $page = I('get.page',1,'int');
        $rows = I('get.rows',10,'int');
        //$db = M('Staffinfo');
        $db = M('classinfo');
        $ret['total'] = $db->count();
        //$firstRow = ($page-1)*$rows;
        $items = $db->order('cid asc')->page($page,$rows)->select();        
        $ret['rows'] = $items;
        $this->ajaxReturn($ret);
    }

    public function cls_new(){
        $ret = array();
        $data = array(
            'id'     => I('post.id','','int'),
            'cid'    => I('post.cid'),
            'name'   => I('post.name'),
            'did'    => I('post.did'),
            'regyear'    => I('post.regyear','2015','int'),
            'status'    => I('post.status',1,'int'),
        );
        $condition['cid'] = $cid;
        $db = M('classinfo');
        $ids = $db->where($condition)->find();
        if(!$ids){
            $data = $db->create($data);
            $db->data($data)->add();
            $ret['success'] = true;
        } else {
            $ret['errorMsg'] = "该记录已经存在，不用添加！"; 
        }        
        $this->ajaxReturn($ret);

    }

    public function cls_edit(){
        $data = array(
            'id'     => I('post.id','','int'),
            'cid'    => I('post.cid'),
            'name'   => I('post.name'),
            'did'    => I('post.did'),
            'regyear'    => I('post.regyear','2015','int'),
            'status'    => I('post.status',1,'int'),
        );

        $ret = array(); 
        $db = M('classinfo');
        $condition['id'] = $id;
        
        $ids = $db->data($data)->save();
        if($ids){        
            $ret['success'] = true;          
        } else {
            $ret['errorMsg'] = $id . "修改出错，只能修改部门名称！";     
                 
        }
        $this->ajaxReturn($ret);


    }

    public function cls_delete(){
        $id = I('post.id',0,'int');
        $ret = array();

        $db = M('classinfo');
        $condition['id'] = $id;
        $ids = $db->where($condition)->delete();
        if($ids>0){
            $ret['success'] = true;
        } elseif ($flag==0) {
            $ret['errorMsg'] = "没有删除数据";
        } else {
            $ret['errorMsg'] = "删除操作出错";
        }
        $this->ajaxReturn($ret);
    }

    public function clsinfo() {
// 导入班级信息表 classinfo
// 班级代码A 班级名称B 专业代码C  专业名称D 系部代码E   系部名称F   招生年份（级）G
// cid       name                             did                        regyear     
        $assc = array(
            'cid' => 'A',
            'name' => 'B',
            'did' => 'E',
            'regyear' => 'G'
        );
        $cnt = 0;
        $tmpArray = array();

        $info = $this->_upload();       
        $file = $info['xls'];
        $path = $file['savepath'] . $file['savename'];

        $tmpArray = $this->_importData($path);
        $cnt = $this->_saveData('classinfo', $assc, 3, $tmpArray, 'cid');
        echo "有" . $cnt . "条班级信息数据，导入成功！";
        echo "<a href='__APP__/Admin/DBase/cls_index/'>点击返回首页</a>";
    }

    // ##################################################  studentinfo

public function stu_index(){
        $this->display();
    }

    public function stulist(){
        $page = I('get.page',1,'int');
        $rows = I('get.rows',10,'int');
        //$db = M('Staffinfo');
        $db = D('StudentView');
        $ret['total'] = $db->count();
        //$firstRow = ($page-1)*$rows;
        $items = $db->order('sid asc')->page($page,$rows)->select();        
        $ret['rows'] = $items;
        $this->ajaxReturn($ret);
    }

    public function stu_new(){
        
        $ret = array();
        $sid = I('post.sid');
        $data = array(            
            'sid'    => $sid,
            'name'   => I('post.name'),
            'sex'    => I('post.sex',1,'int'),
            'cid'    => I('post.cid'),
            'livein'    => I('post.livein',1,'int'),            
            'password' => '666666',
            'time_at' => time()
        );
        $condition['sid'] = $sid;
        $db = M('studentinfo');
        $ids = $db->where($condition)->find();
        if(!$ids){
            $data = $db->create($data);
            $ids1 = $db->data($data)->add();
            if($ids1>0){
                $ret['success'] = true;
            }else{
                $ret['errorMsg'] = "添加数据库失败！";
            }            
        } else {
            $ret['errorMsg'] = "该记录已经存在，不用添加！"; 
        }        
        $this->ajaxReturn($ret);

    }

    public function stu_edit(){
        $id = I('post.id');
        $data = array(            
            'sid'    => I('post.sid','','int'),
            'name'   => I('post.name'),
            'sex'    => I('post.sex',1,'int'),
            'cid'    => I('post.cid'),
            'livein'    => I('post.livein',1,'int'), 
        );

        $ret = array(); 
        $db = M('studentinfo');
        $condition['id'] = $id;
        
        $ids = $db->data($data)->save();
        if($ids){        
            $ret['success'] = true;          
        } else {
            $ret['errorMsg'] = $id . "修改出错，只能修改部门名称！";     
                 
        }
        $this->ajaxReturn($ret);


    }

    public function stu_delete(){
        $id = I('post.id',0,'int');
        $ret = array();

        $db = M('studentinfo');
        $condition['id'] = $id;
        $ids = $db->where($condition)->delete();
        if($ids>0){
            $ret['success'] = true;
        } elseif ($flag==0) {
            $ret['errorMsg'] = "没有删除数据";
        } else {
            $ret['errorMsg'] = "删除操作出错";
        }
        $this->ajaxReturn($ret);
    }

    public function getClSTree(){
        // 生成combotree所用的json
       
        $dep = M('depinfo')->getField('did,name');        
        $db = M('classinfo');
        $ret = array();
        foreach ($dep as $key => $value) { 
            $pnode = array();           
            $pnode['id'] = $key;
            $pnode['text'] = $value; 
            $cond['did'] = $key;   

            $cnode = $db->where('did=' . $key)->getField('cid,name');
            foreach($cnode as $key => $value){ 
                $dnode = array();               
                $dnode['id'] = $key;
                $dnode['text'] = $value;
                $pnode['children'][] = $dnode;
            }            
            $ret[] = $pnode;
        }
        
        $this->ajaxReturn($ret);
        /**
        array(
            array('id'=>11,'text'=>'java','children'=>array(
                array('id'=>111,'text'=>'java01'),
            ),
            array('id'=>22,'text'=>'cpp', 'children'=>array()),
        );
        **/
    }


    public function studentinfo() {
    // 导入班级学生表 studentinfo
    // 学生学号A    学生姓名B   学生性别C   班级代码D   
    // sid          name            sex             cid     
        $assc = array(
            'sid' => 'A',
            'name' => 'B',
            'sex' => 'C',
            'cid' => 'D'
        );

        $info = $this->_upload();       
        $file = $info['xls'];
        $path = $file['savepath'] . $file['savename'];

        $cnt = 0;
        $tmpArray = array();
        $tmpArray = $this->_importData($path, 3, 'A');
        $cnt = $this->_saveData('studentinfo', $assc, 3, $tmpArray, 'sid');
        echo "有" . $cnt . "条班级学生信息数据，导入成功！";
        echo "<a href='__APP__/Admin/DBase/stu_index'>点击返回首页</a>";
    }    

    // ##############################################
    
    // 将本地模板数据导入到uploads目录下。
    private function _upload(){
        
        $config = array(
            'maxSize' => 20971520,
            'rootPath' => './UPLOADS/',
            'savePath' => '',
            'saveName' => array('uniqid',''),
            'exts'     => array('csv','xls','xlsx'),
            'autoSub'  => true,
            'subName'  => array('date','Ymd'),
        );
        $upload = new \Think\Upload($config);
        
        $info = $upload->upload();
        if(!$info){
            $this->error($upload->getError());
        } else {
            /*
            foreach ($info as $file) {
                echo $file['savepath'] . $file['savename'];
            }*/
            return $info;
        }
    }

    public function pollment() {
// 导入评价活动表格到表中 pollment
// 表格 问卷编号A 问卷标题B 类型(辅导员1，班主任2）C 开始时间D 结束时间E 最大评价次数F 学生权重G 系部权重H	学院权重I
//   ：pollid      name        type                   begin     end      maxcnt        quantity1 quantity2  quantity3

        
        
        $cnt = 0;
        $tmpArray = array();
        $assc = array(
            'pollid' => 'A',
            'name' => 'B',
            'type' => 'C',
            'begin' => 'D',
            'end' => 'E',
            'maxcnt' => 'F',
            'quantity1' => 'G',
            'quantity2' => 'H',
            'quantity3' => 'I'
        );
        $tmpArray = $this->_importData("0pollment_basic.xlsx");
        $cnt = $this->_saveData('pollment', $assc, 3, $tmpArray, 'pollid');

        echo "有" . $cnt . "条问卷活动信息数据，导入成功！";
        echo "<a href='/index.php'>点击返回首页</a>";
    }

    public function pollitem() {
// 导入评价活动表格到表中 pollitem
// 表格 评价问卷编号A  内容类别C  分项标题D   标准说明E	 分值F	  排序G
//   ：pollid          conttype     title     description     score   level

        $cnt = 0;
        $tmpArray = array();
        $assc = array(
            'pollid' => 'A',
            'conttype' => 'C',
            'title' => 'D',
            'description' => 'E',
            'score' => 'F',
            'level' => 'G'
        );
        $pk = array('title', 'pollid');
        $tmpArray = $this->_importData("0pollment_item.xlsx");
        $cnt = $this->_saveData('pollitem', $assc, 3, $tmpArray, $pk);

        echo "有" . $cnt . "条问卷活动信息数据，导入成功！";
        echo "<a href='/index.php'>点击返回首页</a>";
    }

    

    

// 
    

// 


    public function clsrelation() {
// 导入班级教辅关系表 clsrelation
// 班级代码B	辅导员工号D	班主任工号F	参与评价（是否）H
// cid          fdyid           bzrid             status     
        $assc = array(
            'pollid' => 'A',
            'cid' => 'B',
            'fdyid' => 'D',
            'bzrid' => 'F',
            'status' => 'H'
        );
        $cnt = 0;
        $tmpArray = array();

        $tmpArray = $this->_importData("5clsrelation.xlsx");
        $cnt = $this->_saveData('clsrelation', $assc, 3, $tmpArray, 'cid');
        echo "有" . $cnt . "条班级教辅信息数据，导入成功！";
        echo "<a href='/index.php'>点击返回首页</a>";
    }

    

    public function cnfile() {
        $objPHPExcel = new \PHPExcel();
        import("Org.Util.PHPExcel.Reader.Excel2007");
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        $filename = "7教工评价关系表v.csv";
        $encode = mb_detect_encoding($keytitle, array("ASCII", "UTF-8", "GB2312", "GBK"));
        echo $encode;
        echo "<br/>";
        echo  "./UPLOADS/" . $filename;
        if ($encode != "utf-8") {
            $cnfile = iconv('ASCII', 'utf-8', $filename);
        }
        $dbasedoc = "./UPLOADS/" . $filename;
        $PHPExcel = $PHPReader->load($dbasedoc);
        echo $daasedoc;

    }

    // ======================================================
    private function _importData($filename, $begin = 3, $colum = null) {

        $objPHPExcel = new \PHPExcel();
        import("Org.Util.PHPExcel.Reader.Excel2007");
        $PHPReader = new \PHPExcel_Reader_Excel2007();

        $dbasedoc = "./UPLOADS/" . $filename;
        $PHPExcel = $PHPReader->load($dbasedoc);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet = $PHPExcel->getSheet(0);
        //获取总列数
        $allColumn = $currentSheet->getHighestColumn();
        //获取总行数
        //$allRow=$currentSheet->getHighestRow();
        $allRow = $currentSheet->getHighestDataRow($colum);
        // echo "allRow :" . $allRow . "<br/>"." allDataRow: ".$allRow1."<br/>";
        $tmpArray = array();
        for ($curRow = $begin; $curRow <= $allRow; $curRow++) {
            for ($curColum = 'A'; $curColum <= $allColumn; $curColum++) {
                $cell = $curColum . $curRow;
                $tmpArray[$curRow][$curColum] = $currentSheet->getCell($cell)->getValue();
            }
        }
        return $tmpArray;
    }

    private function _saveData($tablename, $assc, $begin, $tmpArray, $pk) {
        $db = M($tablename);
        $cnt = 0;
        $end = count($tmpArray) + $begin;
        //echo $end;
        //echo "<br/>";
        //dump($tmpArray);
        for ($i = $begin; $i < $end; $i++) {
            foreach ($assc as $key => $value) {
                $data[$key] = $tmpArray[$i][$value];
            }
            if (!$db->create($data)) {
                exit($db->getError());
            }

            if (is_array($pk) && count($pk) > 0) {
                foreach ($pk as $value) {
                    $condition[$value] = $data[$value];
                }
            } else {
                $condition[$pk] = $data[$pk];
            }

            $flag = $db->where($condition)->find();
            if (count($flag) > 0) {
                continue;
            } else {
                $db->add();
                $cnt+=1;
            }
        }
        return $cnt;
    }

    public function downintofile() {
        $objExcel = "./UPLOADS/TMP/";
        $objWriter = new \PHPExcel_Writer_Excel2007($objExcel); // 用于 2007 
        $objWriter->setOffice2003Compatibility(true);
    }

    private function _downfromurl($outfile = null) {
        //// 到浏览器
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check = 0, pre-check = 0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

}
